const { Sequelize, DataTypes } = require('sequelize');
const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'xiaomiyoupin',
  charset: 'utf8mb4'
};

// 小米有品分类数据
const categoryData = [
  { 
    "name": "有品推荐", 
    "sort": -9223372036854776000, 
    "id": "5fe309e096ee9288ed504dd1", 
    "type": 2 
  }, 
  { 
    "name": "AI科技", 
    "sort": 1610968402826, 
    "id": "6887329d67e7ad0001793461", 
    "type": 0 
  }, 
  { 
    "name": "手机数码", 
    "sort": 1610970165489, 
    "id": "617a3ca6352df300015fe51e", 
    "type": 0 
  }, 
  { 
    "name": "小米自营", 
    "sort": 1610972348033, 
    "id": "631aae35352df30001fcc074", 
    "type": 0 
  }, 
  { 
    "name": "小米电视", 
    "sort": 1610974327561, 
    "id": "617a42a0799ab5000191f061", 
    "type": 0 
  }, 
  { 
    "name": "电脑办公", 
    "sort": 1610974593201, 
    "id": "617a4773799ab5000191f0aa", 
    "type": 0 
  }, 
  { 
    "name": "大家电", 
    "sort": 1610976134593, 
    "id": "631aae88799ab5000191182e", 
    "type": 0 
  }, 
  { 
    "name": "小家电", 
    "sort": 1610978565594, 
    "id": "632d5dfe799ab50001911864", 
    "type": 0 
  }, 
  { 
    "name": "美食酒饮", 
    "sort": 1610988327888, 
    "id": "60053904cff47e0001da16aa", 
    "type": 0 
  }, 
  { 
    "name": "日用百货", 
    "sort": 1610989204512, 
    "id": "60053938cff47e0001da16b8", 
    "type": 0 
  }, 
  { 
    "name": "家具家装", 
    "sort": 1610991405161, 
    "id": "60053912cff47e0001da16ac", 
    "type": 0 
  }, 
  { 
    "name": "服装配饰", 
    "sort": 1610992847920, 
    "id": "617a4804352df300015fe558", 
    "type": 0 
  }, 
  { 
    "name": "母婴亲子", 
    "sort": 1635405439492, 
    "id": "60053942cff47e0001da16bc", 
    "type": 0 
  }, 
  { 
    "name": "有品海购", 
    "sort": 1635405686416, 
    "id": "6005395bcff47e0001da16c2", 
    "type": 0 
  }, 
  { 
    "name": "手表首饰", 
    "sort": 1635406281421, 
    "id": "617a48b7799ab5000191f0be", 
    "type": 0 
  }, 
  { 
    "name": "出行车品", 
    "sort": 1635407325645, 
    "id": "6005391ecff47e0001da16ae", 
    "type": 0 
  }, 
  { 
    "name": "家纺厨具", 
    "sort": 1635408081644, 
    "id": "6005394ccff47e0001da16be", 
    "type": 0 
  }, 
  { 
    "name": "美妆个护", 
    "sort": 1635412227969, 
    "id": "617a49e5799ab5000191f0de", 
    "type": 0 
  }, 
  { 
    "name": "鞋靴箱包", 
    "sort": 1637116529465, 
    "id": "6005393dcff47e0001da16ba", 
    "type": 0 
  }, 
  { 
    "name": "医疗健康", 
    "sort": 1637116832087, 
    "id": "6005392ecff47e0001da16b4", 
    "type": 0 
  }, 
  { 
    "name": "运动户外", 
    "sort": 1637117149533, 
    "id": "60053933cff47e0001da16b6", 
    "type": 0 
  }, 
  { 
    "name": "宠物生活", 
    "sort": 1648541439768, 
    "id": "60053955cff47e0001da16c0", 
    "type": 0 
  }, 
  { 
    "name": "品牌墙", 
    "sort": 9223372036854776000, 
    "id": "5fe309e396ee9288ed504dd3", 
    "type": 1 
  } 
];

async function importCategories() {
  let connection;
  
  try {
    // 创建数据库连接
    connection = await mysql.createConnection(dbConfig);
    console.log('数据库连接成功');

    // 清空现有分类数据（可选）
    console.log('清空现有分类数据...');
    await connection.execute('DELETE FROM categories WHERE 1=1');
    await connection.execute('ALTER TABLE categories AUTO_INCREMENT = 1');

    // 准备插入数据
    const insertData = categoryData.map((item, index) => {
      // 根据type字段确定分类类型
      // type: 0-普通分类, 1-品牌墙, 2-推荐位
      let level = 1;
      let parentId = 0;
      let isShow = 1;
      
      // 特殊处理某些分类
      if (item.type === 2) {
        // 推荐位类型，可能需要特殊处理
        isShow = 1;
      } else if (item.type === 1) {
        // 品牌墙类型
        isShow = 1;
      }

      // 处理排序值，确保在合理范围内
      let sortOrder = index + 1; // 使用索引作为排序值
      if (item.sort && typeof item.sort === 'number') {
        // 如果原始sort值是负数，放在前面
        if (item.sort < 0) {
          sortOrder = 1;
        } else if (item.sort > 0) {
          // 将大的sort值映射到合理范围
          sortOrder = Math.min(Math.floor(item.sort / 100000000), 999) + 10;
        }
      }

      return [
        item.name,
        parentId,
        level,
        sortOrder,
        null, // icon
        null, // image
        `小米有品${item.name}分类`, // description
        isShow,
        new Date(),
        new Date()
      ];
    });

    // 批量插入数据
    console.log('开始插入分类数据...');
    let insertedCount = 0;
    
    for (const data of insertData) {
      const insertSql = `
        INSERT INTO categories 
        (name, parent_id, level, sort_order, icon, image, description, is_show, created_at, updated_at) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      
      await connection.execute(insertSql, data);
      insertedCount++;
    }
    console.log(`成功插入 ${insertedCount} 条分类数据`);

    // 查询插入的数据
    const [rows] = await connection.execute('SELECT * FROM categories ORDER BY sort_order ASC');
    console.log('插入的分类数据:');
    console.table(rows);

  } catch (error) {
    console.error('导入分类数据失败:', error);
  } finally {
    if (connection) {
      await connection.end();
      console.log('数据库连接已关闭');
    }
  }
}

// 执行导入
importCategories();